﻿using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Wimm.Common;
using Wimm.Common.Entities;
using Wimm.Common.Entities.Utils;
using Wimm.Common.Interfaces.Repositories;
using Wimm.Common.Interfaces.Utils;

namespace Wimm.DAL.Repositories
{
    public class ExpenseRepository:SqlLightBaseRepository, IExpenseRepository
    {
        public ExpenseRepository(SQLiteAsyncConnection connection)
            :base(connection)
        {
            
        }

        public ExpenseRepository()
            :base()
        {
        }

        public async Task<IPagedResult<Expense>> LoadPaged(IPagedRequest<Expense> request)
        {
            AsyncTableQuery<Expense> tableQuery = null;

            if (request.SortOrder == "asc")
            {
                if (request.SortKey == "date")
                {
                    tableQuery = _connection.Table<Expense>().OrderBy((u) => u.Time);
                }
                else if (request.SortKey == "category")
                {
                    tableQuery = _connection.Table<Expense>().OrderBy((u) => u.ExpenseCategoryId);
                }
                else
                {
                    tableQuery = _connection.Table<Expense>().OrderBy((u) => u.Id);
                }
            }
            else
            {
                if (request.SortKey == "date")
                {
                    tableQuery = _connection.Table<Expense>().OrderByDescending((u) => u.Time);
                }
                else if (request.SortKey == "category")
                {
                    tableQuery = _connection.Table<Expense>().OrderByDescending((u) => u.ExpenseCategoryId);
                }
                else
                {
                    tableQuery = _connection.Table<Expense>().OrderByDescending((u) => u.Id);
                }
            }

            IEnumerable<Expense> expenses = await tableQuery.Where(request.Where)
                .Skip(request.PageNumber * request.PageSize).Take(request.PageSize).ToListAsync();

            int rowCount = await _connection.Table<Expense>().CountAsync();

            return new GenericPagedResult<Expense>(request.PageNumber, request.PageSize, (rowCount / request.PageSize) + 1, expenses);
        }

        public override async Task<bool> SeedData()
        {
            bool seedIsNeeded = await base.SeedData();

            if (seedIsNeeded)
            {
                var res1 = await _connection.CreateTablesAsync<Icon, ExpenseCategory, Account>();
                var res2 = await _connection.CreateTablesAsync<Transaction, Expense>();

                Icon foodIcon      = new Icon("food.png");
                Icon transportIcon = new Icon("transport.png");
                Icon cosmeticIcon  = new Icon("cosmetics.jpg");
                Icon sportIcon     = new Icon("sports.png");
                Icon clothesIcon   = new Icon("clothes.png");
                Icon carIcon       = new Icon("car.png");
                Icon healthIcon         = new Icon("Health.png");
                Icon electricityIcon    = new Icon("FlatElectricity.png");
                Icon flatWaterIcon      = new Icon("water.png");
                Icon rentIcon           = new Icon("rent.png");
                Icon giftsIcon          = new Icon("gifts.png");
                Icon communictaionIcont = new Icon("Communication.png");
                Icon internetIcon       = new Icon("Internet.png");
                Icon cafeIcon           = new Icon("coffee.png");
                Icon petsIcon           = new Icon("pets.png");
                Icon taxiIcon           = new Icon("taxi.png");
                Icon entertaimentsIcon  = new Icon("Entertainment.png");
                Icon returnIcon         = new Icon("back.png");
                Icon appartmentIcon     = new Icon("appartment.png");
                Icon childrenIcon       = new Icon("children.jpg");
                Icon childclothesIcon   = new Icon("clothesshoes.jpg");
                Icon doctorsIcon        = new Icon("doctors.png");
                Icon nutritionIcon      = new Icon("nutrition.png");
                Icon hygienIcon         = new Icon("hygien.png");
                Icon toysIcon           = new Icon("toys.png");
                Icon educationIcon      = new Icon("education.png");
                Icon childsportsIcon    = new Icon("sportschild.png");
                Icon carWashIcon        = new Icon("carWash.png");
                Icon parkingIcon        = new Icon("Parking.png");
                Icon finesIcon          = new Icon("fines.png");
                Icon repairIcon         = new Icon("repair.png");
                Icon fuelIcon           = new Icon("fuel.png");
                Icon veterinaryIcon     = new Icon("veterinary.png");
                Icon petFoodIcon        = new Icon("petFood.png");
                Icon otherspetsIcon     = new Icon("otherspets.png");
                Icon moviesIcon         = new Icon("Movie.png");
                Icon vacationIcon       = new Icon("vacation.png");
                Icon restaurantsIcon    = new Icon("restaurant.png");
                Icon clubsIcon          = new Icon("bars-clubs.png");
                Icon accessoriesIcon    = new Icon("accessories.png");
                Icon shoesIcon          = new Icon("shoes.png");
                Icon clothessIcon       = new Icon("clothess.png");
                Icon airTicketsIcon     = new Icon("air_rail.png");
                Icon medicineIcon       = new Icon("medicine.png");
                Icon hospitalIcon       = new Icon("hospital.png");
                Icon dentistryIcon      = new Icon("dentistry.png");
                Icon doctorIcon         = new Icon("doctor.png");
                Icon educatIcon         = new Icon("educat.png");
                Icon booksIcon          = new Icon("books.png");
                Icon telephonIcon       = new Icon("telephon.png");
                Icon insuranceIcon      = new Icon("insurance.png");
                Icon massageIcon        = new Icon("Massage.png");
                Icon beautyIcon         = new Icon("health_beauty.png");
                Icon fitnessIcon        = new Icon("fitness.png");
                Icon depilatiionIcon    = new Icon("epilace.png");
                Icon manicureIcon       = new Icon("manicure.png");
                await _connection.InsertAllAsync(new Icon[] { foodIcon, transportIcon, cosmeticIcon, sportIcon, clothesIcon, 
                    carIcon, healthIcon, electricityIcon, flatWaterIcon, rentIcon, giftsIcon, communictaionIcont, internetIcon,
                    cafeIcon, petsIcon, taxiIcon, entertaimentsIcon, returnIcon, appartmentIcon, childrenIcon, childclothesIcon,
                    doctorsIcon, nutritionIcon, hygienIcon, toysIcon, educationIcon, childsportsIcon, carWashIcon, parkingIcon,
                    finesIcon, repairIcon, fuelIcon, veterinaryIcon, petFoodIcon, otherspetsIcon, moviesIcon, vacationIcon, 
                    restaurantsIcon, clubsIcon, accessoriesIcon, shoesIcon, clothessIcon, airTicketsIcon, medicineIcon, 
                    hospitalIcon, dentistryIcon, doctorIcon, educatIcon, booksIcon, telephonIcon, insuranceIcon, massageIcon,
                    beautyIcon, fitnessIcon, depilatiionIcon, manicureIcon});

                ExpenseCategory food  = new ExpenseCategory("Food", foodIcon.Id, "ExpenseFood");
                ExpenseCategory transportParent = new ExpenseCategory("Transport", transportIcon.Id, "ExpenseTransport");
                ExpenseCategory cosmeticsParent = new ExpenseCategory("Care", cosmeticIcon.Id, "ExpenseCare");
                ExpenseCategory clothesParent = new ExpenseCategory("Clothes", clothesIcon.Id, "ExpenseClothes");
                ExpenseCategory carParent = new ExpenseCategory("Car", carIcon.Id, "ExpenseCar");
                ExpenseCategory healthParent = new ExpenseCategory("Health", healthIcon.Id, "ExpenseHealth");
                ExpenseCategory educatParent = new ExpenseCategory("Education", educatIcon.Id, "ExpenseEducation");
                ExpenseCategory flatParent = new ExpenseCategory("Appartment", appartmentIcon.Id, "ExpenseAppartment");
                ExpenseCategory gifts = new ExpenseCategory("Gifts", giftsIcon.Id, "ExpenseGifts");
                ExpenseCategory communicationParent = new ExpenseCategory("Communication", communictaionIcont.Id, "ExpenseCommunication");
                ExpenseCategory petsParent = new ExpenseCategory("Pets", petsIcon.Id, "ExpensePets");
                ExpenseCategory entertaimentParent = new ExpenseCategory("Leisure and rest", entertaimentsIcon.Id, "ExpenseLeisure");
                ExpenseCategory childrenParent = new ExpenseCategory("Children", childrenIcon.Id, "ExpenseChildren");
                ExpenseCategory insurance = new ExpenseCategory("Insurance", insuranceIcon.Id, "ExpenseInsurance");
                await _connection.InsertAllAsync(new ExpenseCategory[] { food, transportParent, cosmeticsParent, clothesParent,
                    carParent, healthParent, educatParent, gifts, communicationParent, petsParent, entertaimentParent, flatParent,
                    childrenParent, insurance});

                ExpenseCategory electricity = new ExpenseCategory("Flat electricity", electricityIcon.Id, "ExpenseAppartmentElectricity", flatParent.Id);
                ExpenseCategory flatWater = new ExpenseCategory("Flat water use", flatWaterIcon.Id, "ExpenseAppartmentWater", flatParent.Id);
                ExpenseCategory flatRent = new ExpenseCategory("Flat rent", rentIcon.Id, "ExpenseAppartmentRent", flatParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] { electricity, flatWater, flatRent });

                ExpenseCategory clothesShoes = new ExpenseCategory("Clothes and Shoes", childclothesIcon.Id, "ExpenseChildrenClothesAndShoes", childrenParent.Id);
                ExpenseCategory doctors = new ExpenseCategory("Doctors", doctorsIcon.Id, "ExpenseChildrenDoctors", childrenParent.Id);
                ExpenseCategory nutrition = new ExpenseCategory("Nutrition", nutritionIcon.Id, "ExpenseChildrenNutrition", childrenParent.Id);
                ExpenseCategory hygien = new ExpenseCategory("Hygiene", hygienIcon.Id, "ExpenseChildrenHygiene", childrenParent.Id);
                ExpenseCategory toys = new ExpenseCategory("Toys", toysIcon.Id, "ExpenseChildrenToys", childrenParent.Id);
                ExpenseCategory education = new ExpenseCategory("Education", educationIcon.Id, "ExpenseChildrenEducation", childrenParent.Id);
                ExpenseCategory childSports = new ExpenseCategory("Hobby and Sports", childsportsIcon.Id, "ExpenseChildrenHobbyAndSports", childrenParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] { clothesShoes, doctors, nutrition, hygien, toys, education, childSports});

                ExpenseCategory carWash = new ExpenseCategory("Car wash", carWashIcon.Id, "ExpenseCarWash", carParent.Id);
                ExpenseCategory parking = new ExpenseCategory("Parking place", parkingIcon.Id, "ExpenseCarParkingPlace", carParent.Id);
                ExpenseCategory fines = new ExpenseCategory("Fines", finesIcon.Id, "ExpenseCarFines", carParent.Id);
                ExpenseCategory repair = new ExpenseCategory("Inspection, car repair", repairIcon.Id, "ExpenseCarInspectionCarRepair", carParent.Id);
                ExpenseCategory fuel = new ExpenseCategory("Fuel", fuelIcon.Id, "ExpenseCarFuel", carParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] {carWash, parking, fines, repair, fuel});

                ExpenseCategory veterinary = new ExpenseCategory("Veterinary", veterinaryIcon.Id, "ExpensePetsVeterinary", petsParent.Id);
                ExpenseCategory petFood = new ExpenseCategory("Pet food", petFoodIcon.Id, "ExpensePetsPetFood", petsParent.Id);
                ExpenseCategory othersPets = new ExpenseCategory("Others", otherspetsIcon.Id, "ExpensePetsOthers", petsParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] {veterinary, petFood, othersPets});

                ExpenseCategory movies = new ExpenseCategory("Movies", moviesIcon.Id, "ExpenseLeisureMovies", entertaimentParent.Id);
                ExpenseCategory vacation = new ExpenseCategory("Vacation", vacationIcon.Id, "ExpenseLeisureVacation", entertaimentParent.Id);
                ExpenseCategory cafe = new ExpenseCategory("Cafe", cafeIcon.Id, "ExpenseLeisureCafe", entertaimentParent.Id);
                ExpenseCategory restaurants = new ExpenseCategory("Restaurants", restaurantsIcon.Id, "ExpenseLeisureRestaurants", entertaimentParent.Id);
                ExpenseCategory clubs = new ExpenseCategory("Clubs and bar", clubsIcon.Id, "ExpenseLeisureClubsAndBar", entertaimentParent.Id);
                ExpenseCategory sports = new ExpenseCategory("Sports", sportIcon.Id, "ExpenseLeisureSports", entertaimentParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] {movies, vacation, cafe, restaurants, clubs, sports});

                ExpenseCategory accessories = new ExpenseCategory("Accessories", accessoriesIcon.Id, "ExpenseClothesAccessoires", clothesParent.Id);
                ExpenseCategory shoes = new ExpenseCategory("Shoes", shoesIcon.Id, "ExpenseClothesShoes", clothesParent.Id);
                ExpenseCategory clothess = new ExpenseCategory("Clothes", clothessIcon.Id, "ExpenseClothesClothes", clothesParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] {accessories, shoes, clothess});

                ExpenseCategory airRailway = new ExpenseCategory("Tickets", airTicketsIcon.Id, "ExpenseTransportTickets", transportParent.Id);
                ExpenseCategory transport = new ExpenseCategory("Public transport", transportIcon.Id, "ExpenseTransportPublicTransport", transportParent.Id);
                ExpenseCategory taxi = new ExpenseCategory("Taxi", taxiIcon.Id, "ExpenseTransportTaxi", transportParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] {airRailway, transport, taxi});

                ExpenseCategory medicine = new ExpenseCategory("Medicine", medicineIcon.Id, "ExpenseHealthMedicine", healthParent.Id);
                ExpenseCategory hospital = new ExpenseCategory("Hospital", hospitalIcon.Id, "ExpenseHealthHospital", healthParent.Id);
                ExpenseCategory dentistry = new ExpenseCategory("Dentistry", dentistryIcon.Id, "ExpenseHealthDentistry", healthParent.Id);
                ExpenseCategory doctor = new ExpenseCategory("Other doctors", doctorIcon.Id, "ExpenseHealthOther", healthParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] { medicine, hospital, dentistry, doctor });

                ExpenseCategory books = new ExpenseCategory("Books", booksIcon.Id, "ExpenseEducationBooks", educatParent.Id);
                ExpenseCategory educatMy = new ExpenseCategory("Education", educatIcon.Id, "ExpenseEducationEducation", educatParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] { books, educatMy });

                ExpenseCategory internet = new ExpenseCategory("Internet and TV", internetIcon.Id, "ExpenseCommunicationInternet", communicationParent.Id);
                ExpenseCategory telephone = new ExpenseCategory("Telephone", telephonIcon.Id, "ExpenseCommunicationTelephone", communicationParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] { internet, telephone });

                ExpenseCategory massage = new ExpenseCategory("SPA, massage", massageIcon.Id, "ExpenseCareSPA", cosmeticsParent.Id);
                ExpenseCategory beauty = new ExpenseCategory("Beauty saloons", beautyIcon.Id, "ExpenseCareBeautySaloons", cosmeticsParent.Id);
                ExpenseCategory fitness = new ExpenseCategory("Fitness, yoga", fitnessIcon.Id, "ExpenseCareFitness", cosmeticsParent.Id);
                ExpenseCategory depilation = new ExpenseCategory("Depilation", depilatiionIcon.Id, "ExpenseCareDepilation", cosmeticsParent.Id);
                ExpenseCategory manicure = new ExpenseCategory("Manicure", manicureIcon.Id, "ExpenseCareManicure", cosmeticsParent.Id);
                await _connection.InsertAllAsync(new ExpenseCategory[] { massage, beauty, fitness, depilation, manicure });
            }

            return await base.SeedData();
        }

        public async Task<List<ExpenseCategory>> LoadAllCategories(int? parentId)
        {
            return await _connection.Table<ExpenseCategory>().Where(c=>c.ParentId == parentId).ToListAsync();
        }

        public async Task<Expense> InsertExpense(Expense newExpense)
        {
            int count = await _connection.InsertAsync(newExpense);

            return newExpense;
        }

        public async Task<ExpenseCategory> LoadCategoryById(int categoryId)
        {
            return await _connection.Table<ExpenseCategory>().Where(c => c.Id == categoryId).FirstOrDefaultAsync();
        }

        public async Task<Expense> EditExpense(Expense expense)
        {
            int count = await _connection.UpdateAsync(expense);

            return expense;
        }
        public async Task<IReadOnlyCollection<CategoryGroup>> LoadGrouped(DateTime? fromFilter, DateTime? toFilter, int userId)
        {
            String sql = @"SELECT ExpenseCategoryId 'CategoryId',SUM(e.Amount/100) 'Amount' FROM Expense e WHERE UserId = ? ";
            List<object> sqlParams = new List<object>();
            sqlParams.Add(userId);

            if (fromFilter.HasValue)
            {
                sql += " AND [Time] >= ?";
                sqlParams.Add(fromFilter.Value.Ticks);
            }
            
            if (toFilter.HasValue)
            {
                sql += " AND [Time] <= ?";
                sqlParams.Add(toFilter.Value.Ticks);
            }

            sql += " GROUP BY e.ExpenseCategoryId ";

            List<CategoryGroup> queryRes =  await _connection.QueryAsync<CategoryGroup>(sql, sqlParams.ToArray());

            return queryRes;
        }

        public async Task<ExpenseCategory> LoadRootCategory(int categoryId)
        {
            String sql = @"SELECT * from ExpenseCategory where Id IN (
                WITH RECURSIVE
                parent_of(id,parentid,name) AS (
                     SELECT Id,ParentId,Name FROM ExpenseCategory WHERE Id = ?
                     UNION 
                     SELECT ec.Id,ec.parentid, ec.name FROM ExpenseCategory ec, parent_of
	                WHERE ec.Id = parent_of.parentid
                  )
                  SELECT id FROM parent_of WHERE parentid IS NULL
                )";

           List<ExpenseCategory> rootCategory = await _connection.QueryAsync<ExpenseCategory>(sql, categoryId);

           return rootCategory.First();
        }

        public async Task<IEnumerable<ExpenseCategory>> LoadCategories()
        {
            return await _connection.QueryAsync<ExpenseCategory>("select distinct ec.* from expenseCategory ec"); 
        }

        public async Task<DateTime> GetLimitDate(bool isMaxDate)
        {
            string sql;
            if (isMaxDate)
            {
                sql = "select * from Expense where time = ( select max(time) from Expense )";
            }
            else
            {
                sql = "select * from Expense where time = ( select min(time) from Expense )";
            }
            var expense = (await _connection.QueryAsync<Expense>(sql)).FirstOrDefault();
            return (expense != null) ? new DateTime(expense.Time) : DateTime.Now;
        }
    }
}
